<?php

/**
 * Created by vscode.
 * User: xiayu
 * Date: 2023/03/16
 * Time: 16:41
 */

namespace app\service;


use think\facade\Db;
use app\logic\AttachmentLogic;
use app\exception\FileIsExistsException;
use app\common\Util;
use app\common\Hash;
// use app\model\Agent;
use app\model\Department;
use app\model\Housing;
use app\model\Building;
use app\model\RoomType;
use app\model\Room;
use app\model\RoomLeaseRecord;
use think\helper\Str;
use app\model\PayRecord;
class ExcelImportService
{
    /**
     * @Description: 导入excel
     * @author: xiayu <cb1071403016@126.com>
     * @Date: 2023-03-17 15:22:50
     * @model：数据库模型
     * @field：字段集，要添加或者更新的字段，例如： $field = ['retailer_code' => '','short_name' => '','full_name' => '',];
     * @isFindField：要查询的字段，如有重复的，就不添加，进行更新操作
     * @user：上传者信息
     * @special：特殊excel类型,调用对应方法
     * @DataStatus：特殊excel类型,转换状态，例如：性别，
     * @isUpdate：如果存在，是否进行更新操作
     * @findMethod：当数据导入成功后，执行最后方法，例如，用户导入成功后，添加入住记录
     * @special_id：当该字段的值为0时，则不进行添加操作
     * @version: 1.0.0
     * @return [type]
     */
    public function updExcel($model, $field, $isFindField = null, $user = null, $special = false, $DataStatus = false,$isUpdate = true,$findMethod = false,$special_id = false)
    {
        $this->Attachment($user);
        $upload_file = $_FILES['file']['tmp_name'];
        $ext = strtolower(pathinfo($_FILES['file']['name'], PATHINFO_EXTENSION));
        if ($ext == 'xlsx') {
            $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
            $spreadsheet = $reader->load($upload_file);
        } else if ($ext == 'xls') {
            $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xls();
            $spreadsheet = $reader->load($upload_file);
        } else {
            throw new FileIsExistsException('导入格式错误，不是excel文件');
        }
        $sheet = $spreadsheet->getActiveSheet();
        $row_count = $sheet->getHighestRow(); //取得总行数
        $cellKey = array(
            'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M',
            'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z',
            'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM',
            'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ'
        );
       
        // if(gettype($isFindField) == 'string'){
        //     $isFindField =  explode(',', $isFindField);
        // }
        
        $res = [];
        // 启动事务
        Db::startTrans();
        try {
            for ($row = 2; $row <= $row_count; $row++) {
                $i = 0;
                $data = [];
                foreach ($field as $key => $value) {
                    $data[$key] = (string)$sheet->getCell($cellKey[$i] . $row)->getValue();
                    $i++;
                }
                // 将状态修改为数据库定义的格式
                if ($DataStatus) {
                    $data = $this->switchDataStatus($DataStatus, $data);
                }
                //执行特殊方法
                if ($special) {
                    $data = $this->switchspecial($special, $data);
                }
                // halt($data);
                if ($isFindField) {
                    $where = [];
                    foreach ($isFindField as $key => $value) {
                        
                        if($key == 'between'){
                            $time = date('Y-m',strtotime($data[$value]));
                            $firstday = $time.'-01 23:59:59';
                            $start = strtotime($time.'-01 00:00:00');
                            $end   = strtotime("$firstday +1 month -1 day");
                            $where[] = [$value,$key,[$start,$end]];
                        }else{
                            if(!empty($data[$value])){
                                $where[] = [$value,$key,$data[$value]];
                            }
                            
                        }
                    }
                    // halt(empty($where));
                    // 查询该信息是否存在数据库
                    $result = $model::where($where)->find();
                    // halt($result);
                    // 如果存在，则进行更新操作
                    if ($result) {
                        if($isUpdate){
                          $res =  $result->save($data);
                        }
                    } else {
                        // 不存在，则进行添加操作
                        if($special_id){
                            if($data[$special_id]){
                                $res =  $model->add($data);
                            }
                        }else{
                            $res =  $model->add($data);
                        }
                       
                    }
                } else {
                    
                    if($special_id){
                        if($data[$special_id]){
                            $res =  $model->add($data);
                        }
                    }else{
                        $res =  $model->add($data);
                    }
                //    $res =  $model->add($data);
                }
                if($findMethod){
                    $res = $result ? $result : $res;
                     $this->switchFindMethod($findMethod, $res,$data);
                }
                // halt($res);
            }
            Db::commit();
            return '导入成功';
        } catch (\Throwable $t) {
            Db::rollback();
            throw $t;
            // return '导入失败';
        }
    }
   
    /**
     * @Description: 判断要执行的是哪个最后方法
     * @author: xiayu <cb1071403016@126.com>
     * @Date: 2023-03-17 16:38:10
     * @version: 1.0.0
     * @return [type]
     */
    public function switchFindMethod($value,$res,$field)
    {
        switch ($value) {
            case 'userFindMethod':
                $field = $this->userFindMethod($res,$field);
                return $field;
                break;
            case 'roomMethod':
                $field = $this->roomMethod($field);
                return $field;
                break;
            default:
                return $field;
        }
    }


    /**
     * @Description: 判断要执行的是那个状态转化方法
     * @author: xiayu <cb1071403016@126.com>
     * @Date: 2023-03-17 16:38:10
     * @version: 1.0.0
     * @return [type]
     */
    public function switchspecial($value, $field)
    {
        switch ($value) {
            case 'userMethod':
                $field = $this->userMethod($field);
                return $field;
                break;
            case 'roomMethod':
                $field = $this->roomMethod($field);
                return $field;
                break;
            case 'roomGetMethod':
                $field = $this->roomGetMethod($field);
                return $field;
                break;
            default:
                return $field;
        }
    }


    
    /**
     * @Description: 判断要执行的是那个状态转化方法
     * @author: xiayu <cb1071403016@126.com>
     * @Date: 2023-03-17 16:38:10
     * @version: 1.0.0
     * @return [type]
     */
    public function switchDataStatus($value, $field)
    {
        switch ($value) {
            case 'userStatus':
                $field = $this->userStatus($field);
                return $field;
                break;
            case 'roomStatus':
                $field = $this->roomStatus($field);
                return $field;
                break;
            default:
                return $field;
        }
    }


     /**
     * @Description: 执行查找房间id房价
     * @author: xiayu <cb1071403016@126.com>
     * @Date: 2023-09-25 17:07:09
     * @version: 1.0.0
     * @return 
     */    
    public function roomGetMethod($field){
        // halt($field);
        $housing_id = Housing::where('name',$field['housing_id'])->value('id');
        if(!$housing_id){
            $field['room_id'] = 0;
        }else{
            $buildingWhere['housing_id'] = $housing_id;
            $buildingWhere['building_number'] = $field['building_id'];
            $building_id = Building::where($buildingWhere)->value('id');
            if(!$building_id){
                $field['room_id'] = 0;
            }else{
            $where['housing_id'] = $housing_id;
            $where['building_number'] = $building_id;
            $where['name']        = $field['room_id'];
            $room_id = Room::where($where)->value('id');
            if(!$room_id){
                $field['room_id'] = 0;
            }else{
                $field['room_id'] = $room_id;
            }
            }
        }
        return $field;
    }   

    /**
     * @Description: 导入用户完成后最后执行的一个方法
     * @author: xiayu <cb1071403016@126.com>
     * @Date: 2023-04-21 17:41:14
     * @version: 1.0.0
     * @return [type]
     */    
    public function userFindMethod($res,$field){
        $payRecord = new PayRecord;
        $roomModel = new Room;
    if($res){
        // 获取小区id
        $housing = Housing::where('name',$field['housing_id'])->find();
        if(!$housing){
          $housing = Housing::create(['name'=>$field['housing_id']]);
        }
        $housing_id = $housing['id'];
        // 获取楼房id
        $buildingWhere['building_number'] = $field['building_id'];
        $buildingWhere['housing_id'] = $housing_id;
        $building = Building::where($buildingWhere)->find();
        if(!$building){
            $building = Building::create(['name'=>$housing['name'],'housing_id'=>$housing_id,'building_number'=>$field['building_id']]);
        }
        $building_id = $building['id'];
        // 获取房间id
        $roomWhere['building_number'] = $building_id;
        $roomWhere['name'] = $field['room_id'];
        $room = Room::where($roomWhere)->find();
        if(!$room){
            $room_type = RoomType::order('id asc')->value('id');
            $room_data = [
                'name'              => $field['room_id'],
                'building_number'   => $building_id,
                'room_type'         => $room_type,
                'housing_id'        => $housing_id
            ];
           $room = Room::create($room_data);
        }
        $room_id = $room['id'];
        // 入住时间
        $start_time = strtotime($field['start_time']);
        // $expire_time = strtotime($field['expire_time']);
            $data = [
                'room_id'       => $room_id,
                'user_id'       => $res['id'],
                'status'        => 1,
                'start_time'    => $start_time,
                // 'expire_time'   => $expire_time,
                'lease_mark'    => $field['lease_mark'],
                'room_lease_mark'    => $field['room_lease_mark'],
                'tenement_price' => $field['propertyPrice'],
                'ele_water_price'=> $field['fee']
            ];
                $roomLeaseWhere['user_id'] = $res['id'];
                $roomLeaseWhere['status']  = 1;
                $record = RoomLeaseRecord::where($roomLeaseWhere)->find();
                if(!$record){
                        RoomLeaseRecord::create($data);
                        $roomWhere['id'] = $room_id;
                        $saveValue = Room::saveValue($roomWhere, 'use_people', 1);
                        Room::where($roomWhere)->update(['sex'=>$res['sex']]);
                }

            // $month = date("Y-m", $start_time);

            // //指定月份第一天的时间戳
            // $lastMonthfirstDay = strtotime(date("Y-m-d 00:0:00", strtotime("first day of {$month}")));

            // // 指定月份最后一天的时间戳
            // $lastMonthLastDay = strtotime(date("Y-m-d 23:59:59", strtotime("{$month} +1 month -1 day")));
            // $findWhere = [];
            // $findWhere[]   = ['fee_date', 'between', [$lastMonthfirstDay,$lastMonthLastDay], 'AND'];
            // $findWhere[]   = ['room_id', '=', $room_id];
            // $findWhere[]   = ['user_id', '=', 0];
            // // $payWhere['']
            // $findRecord = $payRecord->getPayRecord($findWhere);
            // // halt($findRecord);
            // // 获取房间费用
            // $roomPriceWhere['id'] = $room_id;
            // $roomPrice = $roomModel->getRoomPrice($roomPriceWhere);
            // // 获取房间的物业费
            // $propertyPrice = $roomModel->getPropertyPrice($roomPriceWhere);
            // if(!$findRecord){
            //     // halt($findRecord);
            //     $data = [
            //         'room_id'           => $room_id,
            //         'fee'               => $roomPrice + $field['fee'] + $propertyPrice,
            //         'room_price'        => $roomPrice,
            //         'property_price'    => $propertyPrice,
            //         'fee_date'          => $start_time,
            //         'water_price'       => $field['fee']/2,
            //         'electricity_price'       => $field['fee']/2,
            //         'status'            => 1
            //     ];
            //     $findRecord  = PayRecord::create($data);
            //     // halt($findRecord);
            //     $userData = [
            //         'room_id'           => $room_id,
            //         'fee'               => $field['fee'] + $propertyPrice,
            //         'room_price'        => $roomPrice,
            //         'property_price'    => $propertyPrice,
            //         'fee_date'          => $start_time,
            //         'water_price'       => $field['fee']/2,
            //         'electricity_price' => $field['fee']/2,
            //         'pid'               =>$findRecord['id'],
            //         'user_id'           =>$res['id'],
            //         'status'            => 1
            //     ];
            //      PayRecord::create($userData);
            // }else{
            //     $findUserWhere = [];
            //     $findUserWhere[]   = ['fee_date', 'between', [$lastMonthfirstDay,$lastMonthLastDay], 'AND'];
            //     $findUserWhere[]   = ['room_id', '=', $room_id];
            //     $findUserWhere[]   = ['user_id', '=', $res['id']];
            //     // $findUserRecord = $payRecord->getPayRecord($findUserWhere);
            //     $findUserRecord = PayRecord::withoutGlobalScope(['pid'])->where($findUserWhere)->order('fee_date','desc')->find();
            //     // halt($findUserRecord);
            //     if(!$findUserRecord){
            //         $userData = [
            //             'room_id'           => $room_id,
            //             'fee'               => $field['fee'] + $propertyPrice,
            //             'room_price'        => $roomPrice,
            //             'property_price'    => $propertyPrice,
            //             'fee_date'          => $start_time,
            //             'water_price'       => $field['fee']/2,
            //             'electricity_price'  => $field['fee']/2,
            //             'pid'                =>$findRecord['id'],
            //             'user_id'           =>$res['id'],
            //             'status'            => 1
            //         ];
            //         PayRecord::where('id',$findRecord['id'])->inc('fee',$field['fee'])->update();
            //         PayRecord::create($userData);
            //     }
            // }
         }
    }

    /**
     * @Description: 执行房间特殊方法
     * @author: xiayu <cb1071403016@126.com>
     * @Date: 2023-04-21 16:29:58
     * @version: 1.0.0
     * @return [type]
     */    
    public function roomMethod($field){
        // 转换小区id
        // halt($field);
        $housing_id = Housing::where('name',$field['housing_id'])->find();
        if(!$housing_id){
            $housing_id = Housing::create(['name'=>$field['housing_id']]);
        }
        $field['housing_id'] = $housing_id['id'];
        // 转换楼房
        $buildingWhere['housing_id']            = $field['housing_id'];
        $buildingWhere['building_number']       = $field['building_number'];
        $building_number = Building::where($buildingWhere)->find();
        if(!$building_number){
            $building_number = Building::create(['building_number'=>$field['building_number'],'housing_id'=> $field['housing_id'],'name'=>$housing_id['name']]);
        }
        $field['building_number'] = $building_number['id'];
        // 转换房型
        $typeWhere['name']  = $field['room_type'];
        $typeWhere['area']  = $field['area'];
        // $typeWhere['alone']  = $field['alone'];
        $typeWhere['amount']  = $field['amount'];
        $roomType = RoomType::where($typeWhere)->find();
        if(!$roomType){
            $data = [
                'name'          => $field['room_type'],
                'area'          => $field['area'],
                // 'alone'         => $field['alone'],
                'amount'        => $field['amount'],
                'live_number'   => $field['live_number']
            ];
            $roomType = RoomType::create($data);
        }
        // halt($roomType);
        $field['room_type'] = $roomType['id'];
        return $field;
    }   

    /**
     * @Description: 执行用户特殊方法
     * @author: xiayu <cb1071403016@126.com>
     * @Date: 2023-04-18 17:18:24
     * @version: 1.0.0
     * @return [type]
     */    
    public function userMethod($field){
        $field['username'] = 'oauth_' . Str::random(10);
        $field['password'] = 'a123456.';
        $field['platform'] = 9;
        $field['nickname'] = $field['real_name'].Str::random(5);
        // $agent_id = Agent::where('nickname',$field['agent_id'])->value('id');
        // 转换用户企业id
        // if($agent_id){
            // $field['agent_id'] = $agent_id;
            // $where['agent_id'] = $field['agent_id'];
            // $where['name']     = $field['department_id'];
            // $department_id  = Department::where($where)->find();
            // if (!$department_id) {
            //     $department_id =  Department::create(['name' => $field['department_id'], 'agent_id' => $field['agent_id']]);
            // }
            // $field['department_id'] = $department_id['id'];
        // }
       
        // halt($field);

        return $field;
    }


    /**
     * @Description: 转换发票支付状态信息
     * @author: xiayu <cb1071403016@126.com>
     * @param [type] $old_order_status
     * @Date: 2023-03-17 15:37:17
     * @version: 1.0.0
     * @return [type]
     */
    public function roomStatus($field)
    {
        switch ($field['alone']) {
            case '是':
                $field['alone'] = 0;
                return $field;
                break;
            case '否':
                $field['alone'] = 1;
                return $field;
                break;
            default:
                return $field['alone'] = 3;
                return $field;
        }
    }


    
   
    /**
     * @Description: 转换商品状态信息
     * @author: xiayu <cb1071403016@126.com>
     * @param [type] $old_order_status
     * @Date: 2023-03-17 15:37:17
     * @version: 1.0.0
     * @return [type]
     */
    public function userStatus($field)
    {
        switch ($field['sex']) {
            case '男':
                $field['sex'] = 1;
                break;
            case '女':
                $field['sex'] = 2;
                break;
                break;
            default:
                $field['sex'] = 0;
                break;
        }
        // switch ($field['place']) {
        //     case '大陆':
        //         $field['place'] = 0;
        //         break;
        //     case '台湾':
        //         $field['place'] = 1;
        //         break;
        //     default:
        //         $field['place'] = 2;
        //         break;
        // }
        return $field;
    }


    /**
     * @Description: 保存上传的excle文件
     * @author: xiayu <cb1071403016@126.com>
     * @param [type] $user      上传者的信息
     * @Date: 2023-03-17 16:12:52
     * @version: 1.0.0
     * @return [type]
     */
    public function Attachment($user)
    {
        $attachmentLogic = new AttachmentLogic();
        if (!$user) {
            $user = [
                'id' => 1
            ];
        }
        $params = [];
        $result = $attachmentLogic->upload();
        if ($result) {
            unset($result[0]['savename'], $result[0]['path']);
            $params = array_merge($params, $result[0]);
        }
        if (empty($params['category'])) {
            $params['category'] = 'un';
        }
        $model = $attachmentLogic->getModel();

        $detail = $model->where(['admin_id' => $user['id'], 'sha1' => $params['sha1']])->find();
        // halt($user['id']);
        if ($detail) {
            // $this->formatModel($detail);
            // throw new FileIsExistsException(['data' => ['detail' => $detail]]);
        } else {
            // halt('我进来了');
            $params['admin_id'] = $user['id'];
            $detail = $model->create($params);
        }
    }
}
